"""
openpyxl Excel操作教程
openpyxl是一个专门用于读写Excel 2010 xlsx/xlsm/xltx/xltm文件的Python库
"""

import openpyxl
import datetime
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.chart import BarChart, Reference
import os

# ==================== 1. 创建新的Excel文件 ====================

def create_new_excel():
    """创建新的Excel工作簿"""
    # 创建工作簿
    wb = Workbook()
    
    # 获取默认的工作表（Sheet）
    ws = wb.active
    ws.title = "员工信息"  # 重命名工作表
    
    # 添加表头
    headers = ["姓名", "部门", "职位", "工资", "入职日期"]
    for col, header in enumerate(headers, 1):
        ws.cell(row=1, column=col, value=header)
    
    # 添加数据
    employees = [
        ["张三", "技术部", "工程师", 15000, "2023-01-15"],
        ["李四", "销售部", "销售经理", 12000, "2023-03-20"],
        ["王五", "人事部", "HR专员", 8000, "2023-05-10"],
        ["赵六", "技术部", "高级工程师", 18000, "2022-11-05"]
    ]
    
    for row, employee in enumerate(employees, 2):
        for col, value in enumerate(employee, 1):
            ws.cell(row=row, column=col, value=value)
    # 添加样式
    # 1.表头加粗，列宽加大
    # 表头加粗
    for col in range(1, len(headers)+1):
        ws.cell(row=1, column=col).font = Font(bold=True)
    # 列宽加大
    for col in range(1, len(headers)+1):
        ws.column_dimensions[ws.cell(row=1, column=col).column_letter].width = 15

    # 表中数据全部居中
    for row in ws.iter_rows(min_row=1, max_row=ws.max_row, min_col=1, max_col=ws.max_column):
        for cell in row:
            cell.alignment = Alignment(horizontal='center', vertical='center')
    
    # 工资大于1000 的底色为黄色
    for row in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=4, max_col=4):
        for cell in row:
            if cell.value > 1000:
                cell.fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')

    # 保存文件
    wb.save("员工信息表.xlsx")
    print("✅ 成功创建员工信息表.xlsx")
    return wb

# ==================== 2. 读取现有Excel文件 ====================

def read_excel_file():
    """读取Excel文件并显示内容"""
    try:
        # 加载工作簿
        wb = openpyxl.load_workbook("员工信息表.xlsx")
        
        # 获取工作表
        ws = wb["员工信息"]
        
        print("\n📊 员工信息表内容：")
        print("-" * 50)
        
        # 读取所有数据
        for row in ws.iter_rows(values_only=True):
            print(row)
        
        # 获取特定单元格的值
        print(f"\n📌 特定单元格信息：")
        print(f"A2单元格（第一个员工姓名）: {ws['A2'].value}")
        print(f"B3单元格（第二个员工部门）: {ws.cell(row=3, column=2).value}")
        
        # 获取最大行和列
        max_row = ws.max_row
        max_col = ws.max_column
        print(f"\n📈 表格尺寸：{max_row}行 × {max_col}列")
        
        return wb
    except FileNotFoundError:
        print("❌ 文件不存在，请先运行创建示例")
        return None

# ==================== 3. 修改Excel文件 ====================

def modify_excel_file():
    """修改Excel文件内容"""
    try:
        wb = openpyxl.load_workbook("员工信息表.xlsx")
        ws = wb["员工信息"]
        
        # 修改特定单元格
        ws['D2'] = 16000  # 修改张三的工资
        ws.cell(row=3, column=4, value=13000)  # 修改李四的工资
        
        # 添加新员工
        new_employee = ["钱七", "财务部", "会计", 9000, datetime.datetime.now().strftime("%Y-%m-%d")]
        next_row = ws.max_row + 1
        for col, value in enumerate(new_employee, 1):
            ws.cell(row=next_row, column=col, value=value)
        
        # 添加统计行
        stats_row = ws.max_row + 2
        ws.cell(row=stats_row, column=1, value="统计信息")
        ws.cell(row=stats_row, column=4, value=f"=SUM(D2:D{next_row-1})")
        
        wb.save("修改后的员工信息表.xlsx")
        print("✅ 成功修改并保存为修改后的员工信息表.xlsx")
        return wb
    except FileNotFoundError:
        print("❌ 文件不存在")
        return None

# ==================== 4. 样式设置 ====================

def apply_styles():
    """应用样式到Excel单元格"""
    try:
        wb = openpyxl.load_workbook("员工信息表.xlsx")
        ws = wb["员工信息"]
        
        # 定义样式
        header_font = Font(name='微软雅黑', size=12, bold=True, color="FFFFFF")
        header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
        header_alignment = Alignment(horizontal="center", vertical="center")
        
        data_font = Font(name='宋体', size=11)
        
        # 设置边框
        thin_border = Border(left=Side(style='thin'), 
                           right=Side(style='thin'), 
                           top=Side(style='thin'), 
                           bottom=Side(style='thin'))
        
        # 应用表头样式
        for cell in ws[1]:
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = header_alignment
        
        # 应用数据样式和边框
        for row in ws.iter_rows(min_row=2, max_row=ws.max_row, 
                               min_col=1, max_col=ws.max_column):
            for cell in row:
                cell.font = data_font
                cell.border = thin_border
        
        # 调整列宽
        for col in ws.columns:
            max_length = 0
            column = col[0].column_letter
            for cell in col:
                try:
                    if len(str(cell.value)) > max_length:
                        max_length = len(str(cell.value))
                except:
                    pass
            adjusted_width = (max_length + 2) * 1.2
            ws.column_dimensions[column].width = adjusted_width
        
        wb.save("带样式的员工信息表.xlsx")
        print("✅ 成功应用样式并保存为带样式的员工信息表.xlsx")
        return wb
    except FileNotFoundError:
        print("❌ 文件不存在")
        return None

# ==================== 5. 创建图表 ====================

def create_chart():
    """在Excel中创建图表"""
    try:
        wb = openpyxl.load_workbook("员工信息表.xlsx")
        ws = wb["员工信息"]
        
        # 创建柱状图
        chart = BarChart()
        chart.title = "员工工资对比"
        chart.x_axis.title = "员工姓名"
        chart.y_axis.title = "工资"
        
        # 设置数据范围
        data = Reference(ws, min_col=4, min_row=1, max_row=ws.max_row, max_col=4)
        categories = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
        
        chart.add_data(data, titles_from_data=True)
        chart.set_categories(categories)
        
        # 将图表添加到工作表
        ws.add_chart(chart, "G2")
        
        wb.save("带图表的员工信息表.xlsx")
        print("✅ 成功创建图表并保存为带图表的员工信息表.xlsx")
        return wb
    except FileNotFoundError:
        print("❌ 文件不存在")
        return None

# ==================== 6. 多工作表操作 ====================

def multiple_sheets():
    """多工作表操作"""
    wb = Workbook()
    
    # 创建多个工作表
    sheet1 = wb.active
    sheet1.title = "销售数据"
    
    sheet2 = wb.create_sheet("财务数据")
    sheet3 = wb.create_sheet("人事数据", 0)  # 插入到第一个位置
    
    # 向不同工作表添加数据
    # 销售数据
    sales_headers = ["月份", "销售额", "利润"]
    for col, header in enumerate(sales_headers, 1):
        sheet1.cell(row=1, column=col, value=header)
    
    sales_data = [
        ["1月", 100000, 30000],
        ["2月", 120000, 36000],
        ["3月", 150000, 45000]
    ]
    
    for row, data in enumerate(sales_data, 2):
        for col, value in enumerate(data, 1):
            sheet1.cell(row=row, column=col, value=value)
    
    # 财务数据
    finance_headers = ["项目", "预算", "实际支出"]
    for col, header in enumerate(finance_headers, 1):
        sheet2.cell(row=1, column=col, value=header)
    
    # 在工作表之间切换和操作
    print(f"工作表列表: {wb.sheetnames}")
    
    wb.save("多工作表示例.xlsx")
    print("✅ 成功创建多工作表示例.xlsx")
    return wb

# ==================== 7. 高级功能：公式和筛选 ====================

def advanced_features():
    """高级功能：公式、筛选等"""
    wb = Workbook()
    ws = wb.active
    ws.title = "高级功能示例"
    
    # 添加数据
    data = [
        ["产品", "单价", "数量", "总价"],
        ["产品A", 100, 5, "=B2*C2"],
        ["产品B", 200, 3, "=B3*C3"],
        ["产品C", 150, 4, "=B4*C4"],
        ["总计", "", "", "=SUM(D2:D4)"]
    ]
    
    for row, row_data in enumerate(data, 1):
        for col, value in enumerate(row_data, 1):
            ws.cell(row=row, column=col, value=value)
    
    # 设置筛选
    ws.auto_filter.ref = ws.dimensions
    
    wb.save("高级功能示例.xlsx")
    print("✅ 成功创建高级功能示例.xlsx")
    return wb

# ==================== 8. 实用的数据处理函数 ====================

def practical_functions():
    """实用的数据处理函数"""
    
    def find_and_replace(filename, sheet_name, find_text, replace_text):
        """查找和替换功能"""
        wb = openpyxl.load_workbook(filename)
        ws = wb[sheet_name]
        
        for row in ws.iter_rows():
            for cell in row:
                if cell.value and find_text in str(cell.value):
                    cell.value = str(cell.value).replace(find_text, replace_text)
        
        wb.save(f"替换后的_{filename}")
        print(f"✅ 完成查找替换: {find_text} -> {replace_text}")
    
    def merge_cells_example():
        """合并单元格示例"""
        wb = Workbook()
        ws = wb.active
        
        ws.merge_cells('A1:D1')
        ws['A1'] = "合并单元格标题"
        ws['A1'].alignment = Alignment(horizontal="center")
        
        wb.save("合并单元格示例.xlsx")
        print("✅ 创建合并单元格示例")
    
    def read_specific_range(filename, sheet_name, range_str):
        """读取特定范围的数据"""
        wb = openpyxl.load_workbook(filename)
        ws = wb[sheet_name]
        
        data = []
        for row in ws[range_str]:
            row_data = [cell.value for cell in row]
            data.append(row_data)
        
        return data
    
    # 使用示例
    merge_cells_example()
    
    return {
        'find_and_replace': find_and_replace,
        'read_specific_range': read_specific_range
    }

# ==================== 主程序 ====================

if __name__ == "__main__":
    print("🚀 openpyxl Excel操作教程")
    print("=" * 50)
    
    # 1. 创建新文件
    # print("\n1. 创建新的Excel文件")
    # create_new_excel()
    
    # # 2. 读取文件
    print("\n2. 读取Excel文件")
    read_excel_file()
    
    # 3. 修改文件
    print("\n3. 修改Excel文件")
    modify_excel_file()
    
    # 4. 应用样式
    print("\n4. 应用样式")
    apply_styles()
    
    # # 5. 创建图表
    # print("\n5. 创建图表")
    # create_chart()
    
    # # 6. 多工作表操作
    # print("\n6. 多工作表操作")
    # multiple_sheets()
    
    # # 7. 高级功能
    # print("\n7. 高级功能")
    # advanced_features()
    
    # # 8. 实用函数
    # print("\n8. 实用函数")
    # practical_functions()
    
    # print("\n" + "=" * 50)
    # print("🎉 教程完成！所有示例文件已创建在当前目录")
    
    # # 显示创建的文件
    # excel_files = [f for f in os.listdir('.') if f.endswith('.xlsx')]
    # print("\n📁 创建的Excel文件:")
    # for file in excel_files:
    #     print(f"  - {file}")